﻿CREATE PROCEDURE [dbo].[proc_Order_Getlist_YSPrice_20141023]
	(
		@CompanyId int,
		@DepId int,
		@StateId int,
		@TypeId int,
		@WayId int,
		@ProcessId int,
		@Stype nvarchar(50),
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int,
		@NOE_Flag int,
		@DateType int,
		@ys_min varchar(50),
		@ys_max varchar(50)
	)
As
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepId

	DECLARE @cSqlOrder nvarchar(4000)
	set @cSqlOrder = ''

	DECLARE @cSql nvarchar(4000),@cRq VARCHAR(20)
	set @cSql = ''
--条件判断开始
If @CompanyId>0
	Set @cSql='CompanyId='+Convert(varchar(10),@CompanyId)
Else
	Set @cSql='(CompanyId>0)'
	
If @bmbh_T<>'' And @bmbh_T Is Not Null
	Set @cSql=@cSql+' And DepId In(Select Id From department Where Left(bmbh,Len('''+Convert(varchar(10),@bmbh_T)+'''))='''+Convert(varchar(10),@bmbh_T)+''')'
	
If @StateId<>0
	Set @cSql=@cSql+' And StateId='+Convert(varchar(10),@StateId)
	
If @TypeId<>0
	Set @cSql=@cSql+' And TypeId='+Convert(varchar(10),@TypeId)
	
If @WayId<>0
	Set @cSql=@cSql+' And WayId='+Convert(varchar(10),@WayId)
	
If @ProcessId<>0
	Set @cSql=@cSql+' And ProcessId='+Convert(varchar(10),@ProcessId)
	
If @Stext<>''
	SET @cSql=@cSql+' And '+@Stype+' like ''%'+@Stext+'%'' '
	
If @NOE_Flag<>100
	Set @cSql=@cSql+' And NOE_Flag='+Convert(varchar(10),@NOE_Flag)
Else
	Set @cSql=@cSql+' And NOE_Flag<>1'
	
If @DateType=1
	Set @cRq = 'FinishDate'
Else
	Set @cRq = 'CreateDate'

if @ys_min<>'' and @ys_max<>'' 
    Set @cSql=@cSql+' And YingShouPrice>='+ @ys_min+' And YingShouPrice<='+@ys_max+' '
	
Set @cSql = @cSql + ' And '+@cRq+'>=@sRq And '+@cRq+'<=@eRq'

--条件判断结束
	Set @cSql=@cSql+')'
	Set @cSql='Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')
	;WITH list As(Select ROW_NUMBER() OVER (Order By '+@cRq+' Desc,TradeDate Desc,id Desc)AS Row
			,Id
			,OId
			,OrderId
			,CusId
			,CusName
			,CusPerson
			,CusPersonTel
			,CusDescription
			,CusEstimate
			,TypeId
			,TypeName
			,WayId
			,WayName
			,ProcessId
			,ProcessName
			,Salesman
			,SalesmanAccount
			,IsNull(FinishDate,''1900-01-01'') As TradeDate
			,CreateDate
			,YingShouPrice
			,IsNull(ShiShouPrice,0) As ShiShouPrice
			,Discount
			,PaymentTypeId
			,PaymentType
			,BankId
			,Bank
			,PaymentDescription
			,OtherDescription
			,DepId
			,CompanyId
			,StateId
			,IsNull((Select Title From Order_State Where Id=[Order].StateId),''---'') As StateName
			,NOE_Flag
		--	,Isnull((select Pizhu from Customer_PiZhu where CustomerId=CusId),'''') as Pizhu --批注内容字段 2015.08.20 
		--,Isnull((select CusState from Customer_PiZhu where CustomerId=CusId),0) as CusState --是否有批注字段 
			From [Order]
			Where '+@cSql
			
	Set @cSql=@cSql+'Select *,0.00 as SumPrice,(Select Count(0) From list) As RecordCount
	From list
	Where Row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex) + '
	Order By Row'
	-- SumPrice:总额
	Exec(@cSql)
End